from flask import Blueprint, render_template, request, jsonify
from flask_login import login_required, current_user
from ..models.database import db
from ..models.models import Grade, Student, Course, Class, College, Teacher
from .tea_auth import teacher_login_required, add_registration
from .tea_utils import get_paginated_results, get_common_data, handle_errors, teacher_required
import pandas as pd
import os

# 创建蓝图
bp = Blueprint('teacher_grade', __name__, url_prefix='/teacher')

def get_base_grade_query(teacher_id=None):
    """
    获取基础成绩查询
    Args:
        teacher_id: 教师ID，如果提供则只查询该教师的成绩
    Returns:
        基础查询对象
    """
    query = db.session.query(
        Grade,
        Student.student_number,
        Student.name.label('student_name'),
        Course.course_name,
        Class.class_name,
        College.college_name
    ).join(
        Student, Grade.student_number == Student.student_number
    ).join(
        Course, Grade.course_id == Course.course_id
    ).join(
        Class, Student.class_id == Class.class_id
    ).join(
        College, Class.college_id == College.college_id
    )
    
    if teacher_id:
        query = query.filter(Grade.teacher_id == teacher_id)
    
    return query

def convert_grade_to_dict(row):
    """
    将成绩查询结果转换为字典
    Args:
        row: 查询结果行，包含(Grade, student_number, student_name, course_name, class_name, college_name)
    Returns:
        成绩信息字典，包含成绩ID、学生信息、课程信息、班级信息、学院信息和分数信息
    """
    grade, student_number, student_name, course_name, class_name, college_name = row
    return {
        'grade_id': grade.grade_id,
        'student_number': student_number,
        'student_name': student_name,
        'course_name': course_name,
        'class_name': class_name,
        'college_name': college_name,
        'semester': grade.semester,
        'regular_score': grade.regular_score,
        'final_score': grade.final_score,
        'total_score': grade.total_score
    }

def validate_grade_data(data, grade_id=None):
    """
    验证成绩数据
    Args:
        data: 成绩数据字典
        grade_id: 成绩ID，用于更新时验证
    Returns:
        (bool, str/dict): 验证结果和错误信息或验证后的数据
    """
    if not data:
        return False, '请求体为空'
    
    required_fields = ['student_number', 'course_id', 'semester', 'regular_score', 'final_score']
    for field in required_fields:
        if field not in data:
            return False, f'缺少必要字段: {field}'
    
    try:
        # 检查学生是否存在
        if not Student.query.filter_by(student_number=data['student_number']).first():
            return False, '学生不存在'
        
        # 检查课程是否存在
        if not Course.query.get(data['course_id']):
            return False, '所选课程不存在'
        
        # 只在新增时检查成绩是否已存在
        if not grade_id:
            existing_grade = Grade.query.filter_by(
                student_number=data['student_number'],
                course_id=data['course_id'],
                semester=data['semester']
            ).first()
            
            if existing_grade:
                return False, '该学生在此学期的这门课程已有成绩'
        
        return True, {
            'student_number': data['student_number'],
            'course_id': int(data['course_id']),
            'semester': data['semester'],
            'regular_score': float(data['regular_score']),
            'final_score': float(data['final_score']),
            'teacher_id': current_user.teacher_id
        }
    except ValueError as e:
        return False, f'数据格式错误: {str(e)}'

# ==================== 页面路由 ====================
@bp.route('/grades')
@teacher_login_required
@add_registration
def grades(registration):
    """
    成绩管理页面
    Args:
        registration: 用户注册信息
    Returns:
        渲染后的成绩管理页面
    """
    page = request.args.get('page', 1, type=int)
    per_page = 20
    
    # 获取所有成绩记录，按成绩ID降序排列
    grades_query = get_base_grade_query().order_by(Grade.grade_id.desc())
    pagination = grades_query.paginate(page=page, per_page=per_page)
    
    # 将查询结果转换为字典列表
    grades_list = [convert_grade_to_dict(row) for row in pagination.items]
    
    # 获取所有学生和课程
    students = db.session.query(
        Student.student_number,
        Student.name
    ).order_by(Student.student_number).all()
    
    courses = db.session.query(
        Course.course_id,
        Course.course_name
    ).order_by(Course.course_id).all()
    
    # 获取通用数据
    common_data = get_common_data()
    common_data['courses'] = courses
    
    return render_template('teacher/grades.html',
                         registration=registration,
                         grades=grades_list,
                         pagination=pagination,
                         students=students,
                         **common_data)

# ==================== API路由 ====================
@bp.route('/api/grades')
@teacher_login_required
def get_grades():
    """
    获取成绩列表或搜索成绩
    Returns:
        成绩列表和分页信息的JSON响应
    """
    search_text = request.args.get('search', '').strip()
    page = request.args.get('page', 1, type=int)
    per_page = 20
    
    # 构建查询，不限制教师ID
    query = get_base_grade_query()
    
    # 如果有搜索文本，添加搜索条件
    if search_text:
        query = query.filter(
            db.or_(
                Student.student_number.ilike(f'%{search_text}%'),
                Student.name.ilike(f'%{search_text}%'),
                Course.course_name.ilike(f'%{search_text}%')
            )
        )
    
    # 分页；排序：按成绩ID降序排列（新添加的在前面）
    query = query.order_by(Grade.grade_id.desc())
    pagination = query.paginate(page=page, per_page=per_page) # paginate 方法生成分页对象
    
    # 转换结果
    grades_list = [convert_grade_to_dict(row) for row in pagination.items]
    
    return jsonify({
        'success': True,
        'grades': grades_list,
        'pagination': {
            'total': pagination.total,
            'pages': pagination.pages,
            'current_page': pagination.page,
            'has_prev': pagination.has_prev,
            'has_next': pagination.has_next,
            'prev_num': pagination.prev_num,
            'next_num': pagination.next_num
        }
    })

@bp.route('/api/grades/<grade_id>', methods=['GET'])
@teacher_login_required
def get_grade(grade_id):
    """
    获取单个成绩
    Args:
        grade_id: 成绩ID
    Returns:
        成绩信息的JSON响应
    """
    grade = Grade.query.get_or_404(grade_id)
    return jsonify({
        'grade_id': grade.grade_id,
        'student_number': grade.student_number,
        'course_id': grade.course_id,
        'semester': grade.semester,
        'regular_score': grade.regular_score,
        'final_score': grade.final_score
    })

@bp.route('/api/grades', methods=['POST'])
@teacher_login_required
@handle_errors
def create_grade():
    """
    创建新成绩
    Returns:
        创建结果的JSON响应
    """
    data = request.get_json()
    is_valid, result = validate_grade_data(data)
    if not is_valid:
        return jsonify({'success': False, 'message': result}), 400
    
    try:
        grade = Grade(**result)
        db.session.add(grade)
        db.session.commit()
        return jsonify({'success': True})
    except Exception as e:
        db.session.rollback()
        return jsonify({'success': False, 'message': f'数据库错误: {str(e)}'}), 400

@bp.route('/api/grades/<grade_id>', methods=['PUT'])
@teacher_login_required
@handle_errors
def update_grade(grade_id):
    """
    更新成绩
    Args:
        grade_id: 成绩ID
    Returns:
        更新结果的JSON响应
    """
    grade = Grade.query.get(grade_id)
    if not grade:
        return jsonify({'success': False, 'message': '成绩不存在'}), 404
    
    data = request.get_json()
    is_valid, result = validate_grade_data(data, grade_id)
    if not is_valid:
        return jsonify({'success': False, 'message': result}), 400
    
    try:
        for key, value in result.items():
            setattr(grade, key, value)
        db.session.commit()
        return jsonify({'success': True})
    except Exception as e:
        db.session.rollback()
        return jsonify({'success': False, 'message': f'数据库错误: {str(e)}'}), 400

@bp.route('/api/grades/<grade_id>', methods=['DELETE'])
@teacher_login_required
@handle_errors
def delete_grade(grade_id):
    """
    删除成绩
    Args:
        grade_id: 成绩ID
    Returns:
        删除结果的JSON响应
    """
    grade = Grade.query.get(grade_id)
    if not grade:
        return jsonify({'success': False, 'message': '成绩不存在'}), 404
    
    try:
        db.session.delete(grade)
        db.session.commit()
        return jsonify({'success': True})
    except Exception as e:
        db.session.rollback()
        return jsonify({'success': False, 'message': f'删除失败: {str(e)}'}), 400

@bp.route('/api/grades/import', methods=['POST'])
@teacher_login_required
@handle_errors
def import_grades():
    """
    从Excel文件批量导入成绩
    Returns:
        导入结果的JSON响应
    """
    if 'file' not in request.files:
        return jsonify({'success': False, 'message': '未上传文件'}), 400
    
    file = request.files['file']
    if file.filename == '':
        return jsonify({'success': False, 'message': '未选择文件'}), 400
    
    if '.' not in file.filename or file.filename.rsplit('.', 1)[1].lower() not in {'xlsx', 'xls'}:
        return jsonify({'success': False, 'message': '只支持Excel文件(.xlsx, .xls)'}), 400
    
    try:
        # 读取Excel文件
        df = pd.read_excel(file)
        
        # 验证必要的列是否存在
        required_columns = ['学号', '平时成绩', '期末成绩']
        missing_columns = [col for col in required_columns if col not in df.columns]
        if missing_columns:
            return jsonify({
                'success': False,
                'message': f'Excel文件缺少必要的列: {", ".join(missing_columns)}'
            }), 400
        
        # 获取其他参数
        class_id = request.form.get('class_id')
        course_id = request.form.get('course_id')
        semester = request.form.get('semester')
        
        if not all([class_id, course_id, semester]):
            return jsonify({'success': False, 'message': '请选择班级、课程和学期'}), 400
        
        # 验证班级和课程是否存在
        if not Class.query.get(class_id):
            return jsonify({'success': False, 'message': '所选班级不存在'}), 400
        if not Course.query.get(course_id):
            return jsonify({'success': False, 'message': '所选课程不存在'}), 400
        
        # 开始导入
        success_count = 0
        error_records = []
        
        for index, row in df.iterrows():
            try:
                student_number = str(row['学号']).strip()
                regular_score = float(row['平时成绩'])
                final_score = float(row['期末成绩'])
                
                # 验证学生是否存在
                student = Student.query.filter_by(student_number=student_number).first()
                if not student:
                    error_records.append(f'第{index+2}行: 学号 {student_number} 不存在')
                    continue
                
                # 验证成绩是否已存在
                existing_grade = Grade.query.filter_by(
                    student_number=student_number,
                    course_id=course_id,
                    semester=semester
                ).first()
                
                if existing_grade:
                    error_records.append(f'第{index+2}行: 学号 {student_number} 在此学期的这门课程已有成绩')
                    continue
                
                # 验证成绩范围
                if not (0 <= regular_score <= 100) or not (0 <= final_score <= 100):
                    error_records.append(f'第{index+2}行: 成绩必须在0-100之间')
                    continue
                
                # 创建新成绩记录
                grade = Grade(
                    student_number=student_number,
                    course_id=course_id,
                    semester=semester,
                    regular_score=regular_score,
                    final_score=final_score,
                    teacher_id=current_user.teacher_id
                )
                db.session.add(grade)
                success_count += 1
                
            except ValueError as e:
                error_records.append(f'第{index+2}行: 数据格式错误 - {str(e)}')
                continue
            except Exception as e:
                error_records.append(f'第{index+2}行: 处理失败 - {str(e)}')
                continue
        
        # 提交事务
        db.session.commit()
        
        return jsonify({
            'success': True,
            'message': f'成功导入 {success_count} 条成绩记录',
            'details': error_records if error_records else None
        })
        
    except Exception as e:
        db.session.rollback()
        return jsonify({'success': False, 'message': f'导入失败: {str(e)}'}), 400